package net.zjitc.dao.impl;

import net.zjitc.dao.ProductDao;
import net.zjitc.domain.Product;
import net.zjitc.utils.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 2 * @Author: 洪瑶
 * 3 * @Date: 2018/10/16 15:31
 * 4
 */
public class ProductDaoImpl implements ProductDao {
    @Override
    public List<Product> sqlfindAll() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from product";
        List<Product>list =(List<Product>) queryRunner.query(sql,new BeanListHandler(Product.class));
        return list;
    }

    @Override
    public List<Product> sqlfindhot() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from product where is_hot = 1 order by shop_price desc limit 0,10";
        List<Product>list =(List<Product>) queryRunner.query(sql,new BeanListHandler(Product.class));
        return list;
    }

    @Override
    public List<Product> sqlfindnew() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from product order by pdate desc limit 0,10";
        List<Product>list =(List<Product>) queryRunner.query(sql,new BeanListHandler(Product.class));
        return list;
    }

    @Override
    public boolean sqlAdd(Product product) throws SQLException {
        QueryRunner runner =  new QueryRunner(C3P0Utils.getDataSource());
        String sql = "insert into product(pname,market_price,shop_price,image,pdesc,is_hot,pdate,csid) values(?,?,?,?,?,?,?,?)";
        int num = runner.update(sql,new Object[]{product.getPname(),product.getMarket_price(),product.getShop_price(),product.getImage(),product.getPdesc(),product.getIs_hot(),product.getPdate(),product.getCsid()});
        if ( num > 0 )
        {
            return true;
        }
        return false;
    }

    @Override
    public boolean sqlDelete(int id) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "delete from product where pid = ?";
        int num = runner.update(sql,id);
        if ( num > 0 )
        {
            return true;
        }
        return false;
    }


    @Override
    public boolean sqlUpdate(Product product, String id) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "update product set pname=?,market_price=?,shop_price=?,image=?,pdesc=?,is_hot=?,pdate=?,csid=? where pid=?";
        int num = runner.update(sql,new Object[]{product.getPname(),product.getMarket_price(),product.getShop_price(),product.getImage(),product.getPdesc(),product.getIs_hot(),product.getPdate(),product.getCsid(),id});
        if ( num > 0 )
        {
            return true;
        }
        return false;
    }

    @Override
    public Product sqlfindbyid(int id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from product where pid=?";
        Product product = queryRunner.query(sql,new BeanHandler<Product>(Product.class),id);
        return product;
    }

    @Override
    public List<Product> findPageList(int page, int pageSize) {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from product limit ?,?";
        try {
            runner.query(sql,new BeanListHandler<>(Product.class),(page-1)*pageSize,pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int findcount() {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select count(*) from product";
        int count = 0;
        try {
            Number num = (Number) runner.query(sql,new ScalarHandler<>());
            count = num.intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public List<Product> findCategoryPageList(int cid, int page, int pageSize) throws SQLException {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from category c,categorysecond cs,product p where c.cid = cs.cid and cs.csid = p.csid and c.cid = ? limit ?, ?";
        List<Product> query = runner.query(sql, new BeanListHandler<Product>(Product.class), cid, (page - 1)*pageSize, pageSize);
        return query;
    }

    @Override
    public List<Product> findCategorySecondPageList(int csid, int page, int pageSize) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from product where csid = ? limit ? , ?";
        List<Product> products = queryRunner.query(sql,new BeanListHandler<Product>(Product.class),new Object[]{csid,(page-1)*pageSize,pageSize});
        return products;

    }


}
